package com.iris.live.services.data.repositories.impl;

import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;

/**
 * @Description:销售漏斗KPI定时任务
 * @author: Tony.Zhang
 * @Date: 2017-10-06 10:14
 **/
@Repository("SaleFunnekCrontabRepositoryImpl")
public class SaleFunnekCrontabRepositoryImpl extends CrontabRepositoryImpl {
    public SaleFunnekCrontabRepositoryImpl() {
    }

    @Override
    public void execute(Map<String, Object> context) throws Exception {
        this.clearMonthData(context);

        //进店线索线索数
        this.updateClueInfo(context);

        this.updateNewClueInfo(context);

        this.updateInroomClueInfo(context);

        this.updateSaleInfo(context);

        this.updateRetailInfo(context);
    }

    /**
     * 同步前清除同步月数据
     * @param context
     * @throws Exception
     */
    private void clearMonthData(Map<String, Object> context) throws Exception{
        String statement = "DELETE FROM t_rpt_data_sale_info WHERE sale_month = DATE_FORMAT(:v_endDate,'%Y%m')";

        Map<String, Object> params = new HashMap<>();
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 线索数
     * @param context
     * @throws Exception
     */
    private void updateClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_rpt_data_sale_info( `permit`, `sale_month`,`vehicle_model_id`,`cust_channel`,`cust_source`, `clue_num`) " +
                "    SELECT `permit`, DATE_FORMAT(:v_endDate,'%Y%m') sale_month, `vehicle_model_id`,`cust_channel`,`cust_source`, SUM(`clue_num`) `clue_num`  " +
                "    FROM (" +
                "       SELECT  a.`许可` `permit`,  a.`车型编号` vehicle_model_id,  a.`渠道` cust_channel, IFNULL(a.`来源`,'') cust_source , IFNULL(COUNT(1),0) clue_num" +
                "       FROM   `lsh导入名单` a " +
                "       WHERE a.`是否线索` = '是' AND a.`是否有效线索` = '是' " +
                "           AND a.`线索创建日期`>=date_sub( date_sub( date_format(:v_endDate, '%Y-%m-%d'), INTERVAL extract(DAY FROM :v_endDate) - 1 DAY ), INTERVAL 2 MONTH )" +
                "           AND NOT EXISTS( SELECT c.`电话号码` FROM `顾客记录汇总` c WHERE( c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) OR (c.`顾客状态`='战败' AND a.`线索创建日期`>IFNULL(c.战败日期,'1970-01-01'))) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码`) " +
                "       GROUP BY a.`许可`,a.`车型编号`,`渠道`,`来源` " +
                "       UNION ALL " +
                "       SELECT a.`许可` `permit`, a.`意向车型A` vehicle_model_id, " +
                "             IF(a.`顾客状态` IN ('活跃','休眠'),a.`顾客状态`,a.`顾客渠道`) `cust_channel`, " +
                "             IF(a.`顾客状态` IN ('活跃','休眠'),'',IFNULL(a.`顾客来源`,'')) `cust_source`," +
                "             IFNULL(COUNT(1),0) `clue_num` " +
                "       FROM `顾客记录汇总` a " +
                "       WHERE (a.`顾客状态` != '战败' OR a.`顾客状态` IS NULL)" +
                "       GROUP BY a.`许可`,a.`意向车型A`,`cust_channel`,`cust_source` " +
                "    ) A" +
                "    WHERE PERMIT IS NOT NULL AND VEHICLE_MODEL_ID IS NOT NULL  AND CUST_CHANNEL IS NOT NULL " +
                "    GROUP BY a.`PERMIT`,a.`VEHICLE_MODEL_ID`,`cust_channel`,`cust_source`" +
                "    ON DUPLICATE KEY UPDATE  clue_num=VALUES(clue_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 新增线索数
     * @param context
     * @throws Exception
     */
    private void updateNewClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT into t_rpt_data_sale_info( `permit`, `sale_month`,`vehicle_model_id`,`cust_channel`,`cust_source`, `new_clue_num`) " +
                "    select permit, sale_month, vehicle_model_id, cust_channel, cust_source, 新增线索数 new_clue_num " +
                "    from (" +
                "       select  a.`许可` permit,  DATE_FORMAT(:v_startDate,'%Y%m') sale_month, a.`车型编号` vehicle_model_id, a.`渠道` `cust_channel`,  IFNULL(a.`来源`,'') `cust_source`,  ifnull(count(1),0) `新增线索数` " +
                "       from   `lsh导入名单` a " +
                "       where a.`是否线索` = '是' and a.`是否有效线索` = '是' " +
                "           and a.`线索创建日期`>=:v_startDate and a.`线索创建日期`<=:v_endDate " +
                "           and not exists( select c.`电话号码` from `顾客记录汇总` c where( c.`顾客状态` <> '战败' or isnull(c.`顾客状态`) or (c.`顾客状态`='战败' and a.`线索创建日期`>ifnull(c.战败日期,'1970-01-01'))) and c.`许可` = a.`许可` and a.`电话号码` = c.`电话号码`)" +
                "       group by `permit`, `sale_month`, `vehicle_model_id`, `cust_channel`, `cust_source` " +
                "    ) a " +
                "    WHERE PERMIT IS NOT NULL AND VEHICLE_MODEL_ID IS NOT NULL AND CUST_CHANNEL IS NOT NULL" +
                "    ON DUPLICATE KEY UPDATE  new_clue_num=VALUES(new_clue_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 进店线索线索数
     * @param context
     * @throws Exception
     */
    private void updateInroomClueInfo(Map<String, Object> context) throws Exception{
        String statement = "INSERT INTO t_rpt_data_sale_info( `permit`, `sale_month`,`vehicle_model_id`,`cust_channel`,`cust_source`," +
                "   `first_cust_flow_num`, `second_cust_flow_num`, `all_inroom_clue_num`, `first_inroom_clue_num`, `second_inroom_clue_num`," +
                "   `first_inroom_drive_num`, `second_inroom_drive_num`) " +
                "   SELECT permit,DATE_FORMAT(:v_startDate,'%Y%m') sale_month,vehicle_model_id,cust_channel,cust_source, " +
                "       SUM(first_cust_flow_num) first_cust_flow_num, " +
                "       SUM(second_cust_flow_num) second_cust_flow_num, " +
                "       SUM(first_inroom_clue_num)+SUM(second_inroom_clue_num) all_inroom_clue_num, " +
                "       SUM(first_inroom_clue_num) first_inroom_clue_num, " +
                "       SUM(second_inroom_clue_num) second_inroom_clue_num, " +
                "       SUM(first_inroom_drive_num) first_inroom_drive_num, " +
                "       IF(SUM(all_inroom_drive_num)-SUM(first_inroom_drive_num)<0,0,SUM(all_inroom_drive_num)-SUM(first_inroom_drive_num)) second_inroom_drive_num " +
                "FROM " +
                "( " +
                "      SELECT   " +
                "        a.`许可` permit,  " +
                "              IFNULL(a.`意向车型A`,0) vehicle_model_id,  " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),a.`顾客再次来源`,a.`顾客来源`) `cust_channel`,   " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),'',IFNULL(a.信息渠道,'')) `cust_source`,  " +
                "        SUM(IF(a.`是否首次到店`='是',1,0)) first_cust_flow_num,  " +
                "        SUM(IF(a.`是否首次到店`='否',1,0)) second_cust_flow_num,  " +
                "        0 first_inroom_clue_num,  " +
                "        0 second_inroom_clue_num,  " +
                "        0 first_inroom_drive_num,  " +
                "        0 all_inroom_drive_num  " +
                "      FROM   `顾客进店记录表` a     " +
                "      WHERE a.`进店日期`>= :v_startDate  AND a.`进店日期`<= :v_endDate   " +
                "      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')   " +
                "      AND IFNULL(`停留时间`, 0) > 0   " +
                "      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))    " +
                "      GROUP BY permit, vehicle_model_id, cust_channel, cust_source  " +
                "      UNION ALL  " +
                "      SELECT   " +
                "        a.`许可` permit,  " +
                "              IFNULL(a.`意向车型A`,0) vehicle_model_id,  " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),a.`顾客再次来源`,a.`顾客来源`) `cust_channel`,   " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),'',IFNULL(a.信息渠道,'')) `cust_source`,  " +
                "        0 first_cust_flow_num,  " +
                "        0 second_cust_flow_num,  " +
                "        COUNT(1)  first_inroom_clue_num,  " +
                "        0  second_inroom_clue_num,   " +
                "        SUM(IFNULL(tDrive.试驾次数, IF(接待记录.`开始试驾时间` IS NULL,0,1)))  first_inroom_drive_num,   " +
                "        0  all_inroom_drive_num   " +
                "      FROM `顾客进店记录表` a   " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "      LEFT JOIN (SELECT 进店编号, COUNT(1) 试驾次数 FROM 顾客试驾记录 WHERE `试驾开始时间` >=  concat(:v_startDate,' 00:00:01')  AND `试驾开始时间` <=  concat(:v_endDate, ' 23:59:59') GROUP BY 进店编号 ) tDrive ON A.进店编号= tDrive.进店编号 " +
                "      LEFT JOIN 临时接待记录 接待记录 ON A.进店编号= 接待记录.进店编号 " +
                "      WHERE a.`进店日期` >=  :v_startDate  AND a.`进店日期` <=  :v_endDate   " +
                "      AND a.`是否首次到店` = '是'  " +
                "      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')   " +
                "      AND IFNULL(`停留时间`, 0 ) > 0   " +
                "      AND a.`顾客来源` IN ('散客','呼入','网络','主动集客','推荐','再购')  " +
                "      AND cust.`顾客姓名`!=''  " +
                "      AND cust.`电话号码`!=''  " +
                "      AND car.`车型`!=''  " +
                "      AND a.`客户级别` NOT LIKE 'E%'  " +
                "      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))   " +
                "      GROUP BY permit, vehicle_model_id, cust_channel, cust_source  " +
                "      UNION ALL  " +
                "      SELECT   " +
                "        a.`许可` permit,  " +
                "              IFNULL(a.`意向车型A`,0) vehicle_model_id,  " +
                "              a.`顾客再次来源` `cust_channel`,   " +
                "              '' `cust_source`,  " +
                "        0 first_cust_flow_num,  " +
                "        0 second_cust_flow_num,  " +
                "        0 first_inroom_clue_num,  " +
                "        COUNT(1)  second_inroom_clue_num,   " +
                "        0  first_inroom_drive_num,   " +
                "        0  all_inroom_drive_num   " +
                "      FROM `顾客进店记录表` a ,  " +
                "      (   " +
                "        SELECT MIN(a.`进店编号`) `进店编号`  " +
                "        FROM `顾客进店记录表` a   " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "      WHERE a.`进店日期` >=  :v_startDate  AND a.`进店日期` <=  :v_endDate   " +
                "      AND a.`是否首次到店` = '否'  " +
                "      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')   " +
                "      AND IFNULL(`停留时间`, 0 ) > 0   " +
                "      AND a.`顾客再次来源` IN ('活跃','休眠')  " +
                "      AND cust.`顾客姓名`!=''  " +
                "      AND cust.`电话号码`!=''  " +
                "      AND car.`车型`!=''  " +
                "      AND a.`客户级别` NOT LIKE 'E%'  " +
                "      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))  " +
                "        GROUP BY a.`顾客编号`  " +
                "      ) sub  " +
                "      WHERE a.`进店编号` = sub.进店编号  " +
                "      GROUP BY permit, vehicle_model_id, cust_channel, cust_source" +
                "      UNION ALL" +
                "      SELECT    " +
                "      a.`许可` permit,   " +
                "      IFNULL(a.`意向车型A`,0) vehicle_model_id,   " +
                "      IF(a.顾客再次来源 IN ('活跃','休眠'),a.`顾客再次来源`,a.`顾客来源`) `cust_channel`,   " +
                "      IF(a.顾客再次来源 IN ('活跃','休眠'),'',IFNULL(a.信息渠道,'')) `cust_source`,  " +
                "      0 first_cust_flow_num,   " +
                "      0 second_cust_flow_num,   " +
                "      0 first_inroom_clue_num,   " +
                "      0 second_inroom_clue_num,    " +
                "      0 first_inroom_drive_num, " +
                "      COUNT(1)  all_inroom_drive_num    " +
                "      FROM `顾客进店记录表` a ,   " +
                "      (    " +
                "        SELECT MIN(a.`进店编号`) `进店编号`   " +
                "        FROM `顾客进店记录表` a    " +
                "        LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`   " +
                "        LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`   " +
                "        WHERE a.`进店日期` >=  :v_startDate  AND a.`进店日期` <=  :v_endDate    " +
                "        AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')    " +
                "        AND IFNULL(`停留时间`, 0 ) > 0    " +
                "        AND cust.`顾客姓名`!=''   " +
                "        AND cust.`电话号码`!=''   " +
                "        AND car.`车型`!=''   " +
                "        AND a.`客户级别` NOT LIKE 'E%'   " +
                "        AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))   " +
                "        AND ( EXISTS (SELECT `进店编号` FROM `顾客试驾记录` tdrive, `lsh车型` tcar  WHERE tdrive.`进店编号`=a.`进店编号` AND tdrive.`试驾车型编号` = tcar.`车型编号` AND tcar.`车型`!='' ) OR " +
                "              EXISTS (SELECT `进店编号` FROM `临时接待记录` 接待记录 WHERE 接待记录.`进店编号`=a.`进店编号` AND 接待记录.开始试驾时间 IS NOT NULL)) " +
                "        GROUP BY a.`顾客编号`   " +
                "      ) sub   " +
                "      WHERE a.`进店编号` = sub.进店编号   " +
                "      GROUP BY permit , vehicle_model_id, cust_channel, cust_source" +
                ") aa WHERE NOT ISNULL(cust_channel) AND cust_channel!='' " +
                "GROUP BY permit,vehicle_model_id,cust_channel,cust_source" +
                "    ON DUPLICATE KEY UPDATE  first_cust_flow_num=VALUES(first_cust_flow_num),second_cust_flow_num=VALUES(second_cust_flow_num)," +
                "    all_inroom_clue_num=VALUES(all_inroom_clue_num),first_inroom_clue_num=VALUES(first_inroom_clue_num),second_inroom_clue_num=VALUES(second_inroom_clue_num)," +
                "    first_inroom_drive_num=VALUES(first_inroom_drive_num),second_inroom_drive_num=VALUES(second_inroom_drive_num)";
        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 销售数据
     * @param context
     * @throws Exception
     */
    private void updateSaleInfo(Map<String, Object> context) throws Exception{
        String statement = "insert INTO t_rpt_data_sale_info( `permit`, `sale_month`,`vehicle_model_id`,`cust_channel`,`cust_source`," +
                "   `quotation_num`, `order_num`, `delivery_num`) " +
                "   SELECT permit, DATE_FORMAT(:v_startDate,'%Y%m') sale_month, vehicle_model_id, cust_channel, cust_source, " +
                "   SUM(报价数) quotation_num, SUM(订单数) order_num, SUM(交车数) delivery_num " +
                "   FROM (" +
                "      SELECT   " +
                "        a.`许可` permit,  " +
                "        a.`意向车型A` vehicle_model_id,  " +
                "        IF(a.顾客再次来源 IN ('活跃','休眠'),a.`顾客再次来源`,a.`顾客来源`) `cust_channel`,   " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),'',IFNULL(a.信息渠道,'')) `cust_source`,  " +
                "        COUNT(1)  `报价数`,  " +
                "        0 `订单数`,  " +
                "        0 `交车数`  " +
                "      FROM 顾客进店记录表 a,  " +
                "      (   " +
                "        SELECT MIN(a.`进店编号`) `进店编号`  " +
                "        FROM `顾客进店记录表` a   " +
                "        LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "        LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "        WHERE a.`进店日期` >=  :v_startDate  AND a.`进店日期` <=  :v_endDate   " +
                //"              AND IFNULL(`停留时间`, 0 ) > 0   " +
                "              AND cust.`顾客姓名`!=''  " +
                "              AND cust.`电话号码`!=''  " +
                "              AND car.`车型`!=''  " +
                "              AND a.`客户级别` NOT LIKE 'E%'  " +
                "              AND a.`是否报价` = '是'  " +
                "        GROUP BY a.`顾客编号`  " +
                "      ) sub  " +
                "      WHERE a.`进店编号` = sub.进店编号  " +
                "      GROUP BY permit, vehicle_model_id, cust_channel, cust_source  " +
                "      UNION ALL  " +
                "      SELECT   " +
                "        a.`许可` permit,  " +
                "        a.`意向车型A` vehicle_model_id,  " +
                "        IF(a.顾客再次来源 IN ('活跃','休眠'),a.`顾客再次来源`,a.`顾客来源`) `cust_channel`,   " +
                "              IF(a.顾客再次来源 IN ('活跃','休眠'),'',IFNULL(a.信息渠道,'')) `cust_source`,  " +
                "        0 `报价数`,  " +
                "        SUM(IF(a.`是否订单`='是',1,0))+SUM(IF(a.`是否订单`='退订',-1,0)) `订单数`,  " +
                "        SUM(IF(a.`是否交车`='是',1,0)) `交车数`  " +
                "      FROM `顾客进店记录表` a   " +
                "      LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号`  " +
                "      LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号`  " +
                "      WHERE a.`进店日期` >=  :v_startDate  AND a.`进店日期` <=  :v_endDate   " +
                //"            AND IFNULL(`停留时间`, 0 ) > 0   " +
                "            AND cust.`顾客姓名`!=''  " +
                "            AND cust.`电话号码`!=''  " +
                "            AND car.`车型`!=''  " +
                "      GROUP BY permit, vehicle_model_id, cust_channel, cust_source" +
                "    ) a " +
                "   WHERE PERMIT IS NOT NULL AND VEHICLE_MODEL_ID IS NOT NULL AND CUST_CHANNEL IS NOT NULL" +
                "   GROUP BY PERMIT,VEHICLE_MODEL_ID,CUST_CHANNEL,CUST_SOURCE" +
                "   ON DUPLICATE KEY UPDATE  quotation_num=VALUES(quotation_num),order_num=VALUES(order_num),delivery_num=VALUES(delivery_num)";

        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }

    /**
     * 零售数
     * @param context
     * @throws Exception
     */
    private void updateRetailInfo(Map<String, Object> context) throws Exception{
        String statement = "insert INTO t_rpt_data_sale_info( `permit`, `sale_month`,`vehicle_model_id`,`cust_channel`,`cust_source`," +
                "   `retail_num`) " +
                "    SELECT  " +
                "      aa.`许可` `PERMIT`,DATE_FORMAT(:v_startDate,'%Y%m') sale_month,aa.`车型编号` `VEHICLE_MODEL_ID`, " +
                "      IF(ff.`顾客状态` IN ('活跃','休眠'),ff.`顾客状态`,IFNULL(ff.`顾客渠道`,ff.`顾客首次渠道`))  `cust_channel`, " +
                "      IF(ff.`顾客状态` IN ('活跃','休眠'),'',IFNULL(ff.`顾客来源`,'')) `cust_source`, " +
                "      SUM(num) `retail_num` " +
                "    FROM ( " +
                "      SELECT " +
                "        quote.`初始报价单号`,quote.`许可`,quote.`车型编号`,COUNT(DISTINCT retail.`开票时间`) num, " +
                "        SUM(IFNULL(quote.折后价, 0) / 1.17) AS 车辆销售价未税, " +
                "        SUM(IFNULL(quote.消费税,0) / 1.17) AS 消费税未税 , " +
                "        quote.`顾客编号` " +
                "      FROM " +
                "        `报价单表` quote, " +
                "        `报价交车表` retail, " +
                "        `报价订单表` quote_order " +
                "      WHERE quote.`交车` = retail.`编号` " +
                "      AND quote.`订单编号` = quote_order.`订单编号` " +
                "      AND quote.`是否还原点` = '是' " +
                "      AND quote.`是否驳回` = '否' " +
                "      AND retail.`开票时间` >= CONCAT(:v_startDate,' 00:00:01') " +
                "      AND retail.`开票时间` <= CONCAT(:v_endDate,' 23:59:59') " +
                "      AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
                "      GROUP BY quote.`初始报价单号` " +
                "      UNION ALL " +
                "      SELECT " +
                "        quote.`初始报价单号`,quote.`许可`,quote.`车型编号`,-COUNT(DISTINCT retail.`退票时间`) num, " +
                "        -SUM(IFNULL(quote.折后价, 0) / 1.17) AS 车辆销售价未税, " +
                "        -SUM(IFNULL(quote.消费税,0) / 1.17) AS 消费税未税 , " +
                "        quote.`顾客编号` " +
                "      FROM " +
                "        `报价单表` quote, " +
                "        `报价交车表` retail, " +
                "        `报价订单表` quote_order " +
                "      WHERE quote.`交车` = retail.`编号` " +
                "      AND quote.`订单编号` = quote_order.`订单编号` " +
                "      AND quote.`是否还原点` = '是' " +
                "      AND quote.`是否驳回` = '否' " +
                "      AND retail.`退票时间` >= CONCAT(:v_startDate,' 00:00:01') " +
                "      AND retail.`退票时间` <= CONCAT(:v_endDate,' 23:59:59') " +
                "      AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
                "      GROUP BY quote.`初始报价单号` " +
                "      UNION ALL " +
                "      SELECT  " +
                "        bb.`初始报价单号`,bb.`许可`,bb.`车型编号`, -COUNT(*) num, " +
                "        -SUM(IFNULL(bb.折后价, 0) / 1.17) AS 车辆销售价未税, " +
                "        -SUM(IFNULL(bb.消费税,0) / 1.17) AS 消费税未税 , " +
                "        bb.`顾客编号` " +
                "      FROM ( " +
                "        SELECT " +
                "          MAX(b.`报价编号`) qid, a.`开票时间` " +
                "        FROM " +
                "          ( " +
                "            SELECT quote.`报价编号` , quote.`许可` , quote.`初始报价单号` , retail.`开票时间`  " +
                "            FROM " +
                "              `报价单表` quote , " +
                "              `报价交车表` retail , " +
                "              `报价订单表` quote_order " +
                "            WHERE quote.`交车` = retail.`编号` " +
                "            AND quote.`订单编号` = quote_order.`订单编号` " +
                "            AND quote.`是否还原点` = '是' " +
                "            AND quote.`是否驳回` = '否' " +
                "            AND retail.`开票时间` >= CONCAT(:v_startDate , ' 00:00:01') " +
                "            AND retail.`开票时间` <= CONCAT(:v_endDate , ' 23:59:59') " +
                "            AND LEFT(quote_order.`销售类型`,1) NOT IN ('B', 'I') " +
                "            AND ISNULL(retail.`交车日期`) " +
                "            AND (ISNULL(retail.`退票时间`) OR (NOT ISNULL(retail.`退票时间`) AND quote.carTypeChange=1 AND quote_order.订单状态='开票C')) " +
                "            AND ISNULL(retail.`换票时间`) " +
                "          ) a " +
                "        JOIN `报价单表` b ON a.`许可` = b.`许可` " +
                "        AND a.`初始报价单号` = b.`初始报价单号` " +
                "        AND b.`是否还原点` = '是' " +
                "        AND b.`是否驳回` = '否' " +
                "        AND b.`报价编号` < a.`报价编号` " +
                "        GROUP BY a.`报价编号` " +
                "      ) aa, " +
                "      `报价单表` bb , " +
                "      `报价交车表` cc " +
                "      WHERE aa.qid = bb.`报价编号` " +
                "      AND bb.`交车` = cc.`编号` " +
                "      AND NOT ISNULL(cc.`开票时间`) " +
                "      AND ISNULL(cc.`退票时间`) " +
                "      AND ISNULL(cc.`换票时间`) " +
                "      AND ISNULL(cc.`交车日期`) " +
                "      AND aa.`开票时间`!=cc.`开票时间` " +
                "      GROUP BY bb.`初始报价单号` " +
                "    ) aa , " +
                "    `lsh车型` dd , " +
                "    `lsh经销商表` ee , " +
                "    `顾客记录汇总` ff " +
                "    WHERE aa.`车型编号` = dd.`车型编号` " +
                "    AND ee.许可_BMBS = aa.许可 " +
                "    AND aa.`顾客编号` = ff.`顾客编号` " +
                "    GROUP BY PERMIT,VEHICLE_MODEL_ID,cust_channel,cust_source " +
                "   ON DUPLICATE KEY UPDATE  retail_num=VALUES(retail_num)";
        Map<String, Object> params = new HashMap<>();
        params.put("v_startDate", context.get("startDate"));
        params.put("v_endDate", context.get("endDate"));
        super.executeStatement(statement, params);
    }
}
